﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

public partial class CreateDataTable : System.Web.UI.Page
{
	private SqlConnection connection = new SqlConnection();

	protected void Page_Load(object sender, EventArgs e)
	{
		if (!Page.IsPostBack)
		{
			CreateDataTableFunction();
		}

	}

	private void CreateDataTableFunction()
	{
		DataTable dt = new DataTable("产品");
		dt.Columns.Add(new DataColumn("产品ID", typeof(Int32)));
		dt.Columns.Add(new DataColumn("产品名称", typeof(string)));
		dt.Columns.Add(new DataColumn("单价", typeof(Single)));
		dt.Columns.Add(new DataColumn("中止", typeof(bool)));

		string strConnectionString = "server=127.0.0.1;uid=sa;pwd=liuxiaozeng;database=NorthwindCHS";
		connection.ConnectionString = strConnectionString;

		try
		{
			connection.Open();
			SqlCommand command = new SqlCommand("SELECT 产品ID, 产品名称,单价,中止 FROM 产品", connection);

			SqlDataReader dr = command.ExecuteReader();

			while (dr.Read())
			{
				float price = Convert.ToSingle(dr["单价"]);
				if (price >= 0 && price <= 10000)
				{
					DataRow row = dt.NewRow();
					row["产品ID"] = dr["产品ID"];
					row["产品名称"] = dr["产品名称"];
					row["单价"] = Convert.ToSingle(dr["单价"]).ToString("#.00");
					dt.Rows.Add(row);
				}

			}

			DataRow row2 = dt.NewRow();
			row2["产品ID"] = 0;
			row2["产品名称"] = "总价";

			Single sum = 0;
			for(int i = 0; i < dt.Rows.Count;i++)
			{
				sum += Convert.ToSingle(dt.Rows[i]["单价"]);
			}
			Single average = sum/dt.Rows.Count;

			row2["单价"] = average;
			dt.Rows.Add(row2);

			GridView1.DataSource = dt;
			GridView1.DataBind();


		}
		catch (Exception ex)
		{
			Response.Write(ex.Message);
		}
	}

	protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
	{
		GridView1.EditIndex = e.NewEditIndex;
		CreateDataTableFunction();
	}
	protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
	{
		GridView1.EditIndex = -1;
		CreateDataTableFunction();
	}
}
